/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 80020 (8.0.20)
 Source Host           : localhost:3306
 Source Schema         : rss2

 Target Server Type    : MySQL
 Target Server Version : 80020 (8.0.20)
 File Encoding         : 65001

 Date: 03/09/2023 21:16:21
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tb_notify
-- ----------------------------
DROP TABLE IF EXISTS `tb_notify`;
CREATE TABLE `tb_notify`  (
  `id` bigint NOT NULL,
  `type_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '通知类型',
  `text` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '通知内容',
  `user_id` bigint NULL DEFAULT NULL COMMENT '用户ID',
  `res_id` bigint NULL DEFAULT NULL COMMENT '资源ID',
  `nread` boolean DEFAULT '0' COMMENT '是否已读',
  `create_time` datetime NULL DEFAULT NULL COMMENT '录入时间',
  `del` tinyint(1) NULL DEFAULT 0 COMMENT '删除标记',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `user_id`(`user_id` ASC) USING BTREE,
  CONSTRAINT `tb_notify_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of tb_notify
-- ----------------------------


-- ----------------------------
-- Table structure for tb_order
-- ----------------------------
DROP TABLE IF EXISTS `tb_order`;
CREATE TABLE `tb_order`  (
  `id` bigint NOT NULL,
  `pay_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '֧?????',
  `order_num` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '???????',
  `user_id` bigint NULL DEFAULT NULL COMMENT '?û?ID',
  `money` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '֧???',
  `create_time` datetime NULL DEFAULT NULL COMMENT '?µ?ʱ?',
  `pay_time` datetime NULL DEFAULT NULL COMMENT '֧??ʱ?',
  `state` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '????״̬',
  `vip_config_id` bigint NULL DEFAULT NULL COMMENT '?ײ?ID',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `user_id`(`user_id` ASC) USING BTREE,
  CONSTRAINT `tb_order_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of tb_order
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for tb_resource
-- ----------------------------
DROP TABLE IF EXISTS `tb_resource`;
CREATE TABLE `tb_resource`  (
  `id` bigint NOT NULL,
  `resource_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '资源名',
  `url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '资源路径',
  `resource_desc` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '资源描述',
  `keywords` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '关键字，多个以::分割',
  `type_id` bigint NULL DEFAULT NULL COMMENT '资源分类ID',
  `user_id` bigint NULL DEFAULT NULL COMMENT '上传者',
  `score` int NULL DEFAULT NULL COMMENT '资源积分',
  `size` bigint NULL DEFAULT NULL COMMENT '资源大小',
  `ext` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '资源扩展名',
  `state` int NULL DEFAULT NULL COMMENT '资源状态， 0 未审核、1 审核通过、-1 审核未通过',
  `reason` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '审核失败原因',
  `user` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '审核人员',
  `core` tinyint(1) NULL DEFAULT 0 COMMENT '是否是精选资源，默认否',
  `create_time` datetime NULL DEFAULT NULL COMMENT '录入时间',
  `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
  `del` tinyint(1) NULL DEFAULT 0 COMMENT '删除标记',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `type_id`(`type_id` ASC) USING BTREE,
  INDEX `user_id`(`user_id` ASC) USING BTREE,
  CONSTRAINT `tb_resource_ibfk_1` FOREIGN KEY (`type_id`) REFERENCES `tb_resource_type` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `tb_resource_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of tb_resource
-- ----------------------------

-- ----------------------------
-- Table structure for tb_resource_type
-- ----------------------------
DROP TABLE IF EXISTS `tb_resource_type`;
CREATE TABLE `tb_resource_type`  (
  `id` bigint NOT NULL,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '分类名称',
  `pid` bigint NULL DEFAULT NULL COMMENT '上级分类ID',
  `create_time` datetime NULL DEFAULT NULL COMMENT '录入时间',
  `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
  `state` tinyint(1) NULL DEFAULT NULL COMMENT '是否生效',
  `del` tinyint(1) NULL DEFAULT 0 COMMENT '删除标识',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `pid`(`pid` ASC) USING BTREE,
  CONSTRAINT `tb_resource_type_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `tb_resource_type` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of tb_resource_type
-- ----------------------------
-- ----------------------------
-- Table structure for tb_score_config
-- ----------------------------
DROP TABLE IF EXISTS `tb_score_config`;
CREATE TABLE `tb_score_config`  (
  `id` bigint NOT NULL,
  `action` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '???',
  `score` int NULL DEFAULT NULL COMMENT '积分',
  `state` tinyint(1) NULL DEFAULT NULL COMMENT '状态',
  `create_time` datetime NULL DEFAULT NULL COMMENT '录入时间',
  `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
  `del` tinyint(1) NULL DEFAULT 0 COMMENT '删除标识',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of tb_score_config
-- ----------------------------
BEGIN;
INSERT INTO `tb_score_config` (`id`, `action`, `score`, `state`, `create_time`, `update_time`, `del`) VALUES (1, '用户注册', 50, 1, '2023-09-03 13:25:18', NULL, 0), (2, '资源上传', 2, 1, '2023-09-03 13:25:37', NULL, 0);
COMMIT;

-- ----------------------------
-- Table structure for tb_svip_config
-- ----------------------------
DROP TABLE IF EXISTS `tb_svip_config`;
CREATE TABLE `tb_svip_config`  (
  `id` bigint NOT NULL,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '?ײ??',
  `price` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '?۸',
  `exp` int NULL DEFAULT NULL COMMENT '过期时间',
  `score` int NULL DEFAULT NULL COMMENT '积分数量',
  `state` tinyint(1) NULL DEFAULT NULL COMMENT '状态',
  `create_time` datetime NULL DEFAULT NULL COMMENT '录入时间',
  `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
  `del` tinyint(1) NULL DEFAULT 0 COMMENT '删除标识',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of tb_svip_config
-- ----------------------------
BEGIN;
INSERT INTO `tb_svip_config` (`id`, `name`, `price`, `exp`, `score`, `state`, `create_time`, `update_time`, `del`) VALUES (100000, '月会员', '10', 30, 80, 1, '2023-09-02 12:45:57', NULL, 0), (100001, '年会员', '100', 365, 1000, 1, '2023-09-02 12:46:40', '2023-09-03 18:18:58', 0), (100002, '永久会员', '300', -1, 3600, 1, '2023-09-02 12:47:10', NULL, 0), (100003, '试用会员', '1', 1, 0, 1, '2023-09-02 12:47:48', NULL, 0);
COMMIT;

-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user`  (
  `id` bigint NOT NULL,
  `tel` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '手机号ֻ',
  `password` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '密码',
  `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '邮箱',
  `avatar` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '头像',
  `nick_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '昵称',
  `real_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '真实型名',
  `birth` date NULL DEFAULT NULL COMMENT '出生日期',
  `sex` enum('m','f','s') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '性别',
  `state` tinyint(1) NULL DEFAULT NULL COMMENT '状态',
  `create_time` datetime NULL DEFAULT NULL COMMENT '录入时间',
  `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
  `del` tinyint(1) NULL DEFAULT 0 COMMENT '删除标识',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;


-- ----------------------------
-- Table structure for tb_user_score
-- ----------------------------
DROP TABLE IF EXISTS `tb_user_score`;
CREATE TABLE `tb_user_score`  (
  `id` bigint NOT NULL,
  `action` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '动作',
  `score` int NULL DEFAULT NULL COMMENT '积分数量',
  `res_id` bigint NULL DEFAULT NULL COMMENT '资源ID',
  `user_id` bigint NULL DEFAULT NULL COMMENT '用户ID',
  `create_time` datetime NULL DEFAULT NULL COMMENT '录入时间',
  `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
  `del` tinyint(1) NULL DEFAULT 0 COMMENT '删除标识',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `user_id`(`user_id` ASC) USING BTREE,
  CONSTRAINT `tb_user_score_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;



SET FOREIGN_KEY_CHECKS = 1;

create table tb_authorize_user(
  id bigint primary key ,
  alipay_user_id varchar(100) comment '支付宝用户ID',
  wx_user_id varchar(100) comment '微信用户ID',
  qq_user_id varchar(100) comment 'QQ用户ID',
  sina_user_id varchar(100) comment '新浪用户ID',
  github_user_id varchar(100) comment 'github 用户ID',
  user_id bigint comment '系统用户ID',
  foreign key(user_id) references tb_user(id)
);
-- 评论表
create table tb_comments (
  id bigint primary key ,
  star int comment '评论星',
  content text comment '评论内容' ,
  user_id bigint comment '评论人' ,
  res_id bigint comment '资源ID' ,
  state int comment '评论信息状态、0 待审核 1 已审核 -1 未通过审核',
  user varchar(50) comment '审核人员' ,
  reason varchar(200) comment '审核失败原因' ,
  create_time datetime comment '评论时间' ,
  `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
  `del` tinyint(1) NULL DEFAULT 0 COMMENT '删除标识',
  foreign key(user_id) references tb_user(id),
  foreign key (res_id) references tb_resource(id)
);
-- 收藏表
create table tb_collect(
  id bigint primary key ,
  user_id bigint comment '收藏人' ,
  res_id bigint comment '资源ID' ,
  create_time datetime comment '收藏时间'
) ;

-- 资源下载表
create table tb_resource_download_history (
   id bigint primary key auto_increment,
   user_id bigint comment '下载人' ,
   res_id bigint comment '资源ID' ,
   create_time datetime comment '下载时间'
) ;

-- 关注表
create table tb_attention (
  id bigint primary key ,
  from_user bigint comment '关注人' ,
  to_user bigint comment '被关注人' ,
  create_time datetime comment '关注时间' ,
  foreign key (from_user) references tb_user(id) ,
  foreign key (to_user) references tb_user(id)
);


create table tb_message(
   id bigint primary key auto_increment ,
   from_user bigint comment '发送消息的用户ID',
   to_user bigint comment '收消息的用户ID' ,
   content text comment '消息内容' ,
   nread boolean default 0 comment '是否已读' ,
   create_time datetime(3) comment '录入时间,精确到毫秒，否则存储到redis中的时间和数据库的时间会差一秒'
);

create table tb_user_black(
  id bigint primary key auto_increment,
  user_id bigint comment '用户ID',
  black_user bigint comment '黑名单用户ID',
  create_time datetime comment '拉黑事件',
  foreign key(user_id) references tb_user(id),
  foreign key(black_user) references tb_user(id)
);


-- 存储过程、用来处理 更多资源 （资源类型带动态条件递归查询问题、SQL无法解决）
delimiter $$
create procedure get_resource_by_uid(in v_offset bigint, in v_rows bigint, in v_user_id bigint)
begin

	declare done int default false ; -- 声明变量、用来控制 游标是否遍历结束
	declare c_id bigint ;            -- 存储查询到的 资源ID
	declare c_resource_name varchar(100); -- 存储查询到的 资源名称
	declare c_ext varchar(50) ;          -- 存储查询到的资源后缀
	declare c_score int ;               -- 存储查询到的资源积分
	declare c_type_id bigint ;          -- 存储查询的资源类型ID
	declare c_type_name varchar(255) ;  -- 存储查询的资源类型名称
	-- 定义游标、存储查询的结果
	declare resource cursor for select id, resource_name, ext, score, type_id from tb_resource
                                where del = 0 and state = 1 and user_id = v_user_id order by id limit v_offset, v_rows ;
    -- 定义游标处理器、当 找不到数据的时候，继续执行代码，且将 done 设置 为 false 表示数据已经提取完成
    declare continue handler for not found set done = true ;

	-- 创建一张临时表、用来存储遍历到的最终结果
	create temporary table if not exists temp_result(
	  id bigint , resource_name varchar(100), ext varchar(50), score int, type_names varchar(255)
	);

-- 打开游标
open resource ;

-- 使用loop循环遍历游标
read_loop: loop
	  -- 提取游标中的数据
	fetch resource into c_id, c_resource_name, c_ext, c_score , c_type_id ;
	  -- 如果结束、则 退出 loop 循环
	if done then
		leave read_loop;
    end if ;

	-- 根据资源类型ID 查询 该类型的所有父类型 （直接用SQL该逻辑无法编写，因为 c_type_id 是一个动态的数据）
    with recursive temp as (
        select id, pid, name from tb_resource_type where id = c_type_id
        union
        select t.id, t.pid, t.name from tb_resource_type t inner join temp p on t.id = p.pid
    ) select group_concat(name separator '::') into c_type_name from temp order by id ;

    -- 将需要的数据写入到临时表中
    insert into temp_result(id, resource_name, ext, score, type_names) values
        (c_id, c_resource_name, c_ext, c_score, c_type_name) ;

    end loop;
    close resource ;
    -- 查询临时表中的所有数据
    select id, resource_name, ext, score, type_names from temp_result ;
    -- 删除临时表
    drop temporary table if exists temp_result ;

end $$
delimiter ;